﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Common;
using System.Reflection;
using System.Linq.Expressions;
using Messenger.DataAccess.Attributes;

namespace Messenger.DataAccess
{
    /// <summary>
    /// Base class interact with data in DB
    /// Created by: Dinh Quoc Vuong
    /// Created date: Sep 30 2011
    /// </summary>
    /// <typeparam name="T">Table class</typeparam>
    public class DBBase<T>
    {
        #region constructors
        public DBBase()
        {

        }
        #endregion

        #region Get data
        /// <summary>
        /// Filter one by data
        /// </summary>
        /// <param name="value">value searching</param>
        /// <param name="propertyFilter"></param>
        /// <returns></returns>
        public T FilterOneDataEqual(object value, Expression<Func<T, object>> propertyFilter)
        {
            T data = default(T);
            using (DbCommand cmd = Connection.GetCommand())
            {
                try
                {
                    string propertyName = GetPropertyNameByExpression(propertyFilter);
                    Type type = typeof(T);
                    PropertyInfo property = type.GetProperty(propertyName);
                    string sql = string.Format("SELECT * FROM [{0}] WHERE [{1}] = @v", type.Name, property.Name);
                    DbParameter param = cmd.CreateParameter();
                    param.ParameterName = "@v";
                    SetParameterValue(param, value);
                    cmd.Parameters.Add(param);

                    cmd.CommandText = sql;
                    cmd.Connection.Open();
                    DbDataReader reader = cmd.ExecuteReader();
                    data = FillReaderToObject<T>(reader);
                    cmd.Connection.Close();
                }
                catch (Exception e)
                {
                    ThrowDataAccessException(e);
                }
                finally
                {

                }
            }
            return data;
        }

        /// <summary>
        /// Filter one by object
        /// </summary>
        /// <param name="propertyFilter"></param>
        /// <returns></returns>
        public T FilterOneEqual(T obj, Expression<Func<T, object>> propertyFilter)
        {
            T data = default(T);
            using (DbCommand cmd = Connection.GetCommand())
            {
                try
                {
                    string propertyName = GetPropertyNameByExpression(propertyFilter);
                    Type type = typeof(T);
                    PropertyInfo property = type.GetProperty(propertyName);
                    string sql = string.Format("SELECT * FROM [{0}] WHERE [{1}] = @v", type.Name, property.Name);
                    DbParameter param = cmd.CreateParameter();
                    param.ParameterName = "@v";
                    SetParameterValue(param, property.GetValue(obj, null));
                    cmd.Parameters.Add(param);

                    cmd.CommandText = sql;
                    cmd.Connection.Open();
                    DbDataReader reader = cmd.ExecuteReader();
                    data = FillReaderToObject<T>(reader);
                    cmd.Connection.Close();
                }
                catch (Exception e)
                {
                    ThrowDataAccessException(e);
                }
                finally
                {

                }
            }
            return data;
        }

        /// <summary>
        /// Filter one 
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="propertiesFilter"></param>
        /// <returns></returns>
        public T FilterOneEqualWithColumns(T obj, params Expression<Func<T, object>>[] propertiesFilter)
        {
            if (propertiesFilter == null || propertiesFilter.Length == 0)
            {
                ThrowMissingWhereClause();
            }

            T data = default(T);
            using (DbCommand cmd = Connection.GetCommand())
            {
                try
                {
                    string whereClase = "";
                    Type type = typeof(T);
                    string sql = string.Format("SELECT * FROM [{0}] WHERE ", type.Name);

                    string connector = " AND ";
                    foreach (Expression<Func<T, object>> propertyFilter in propertiesFilter)
                    {
                        string propertyName = GetPropertyNameByExpression(propertyFilter);
                        PropertyInfo property = type.GetProperty(propertyName);
                        DbParameter param = cmd.CreateParameter();
                        param.ParameterName = "@" + propertyName;
                        SetParameterValue(param, property.GetValue(obj, null));
                        cmd.Parameters.Add(param);
                        whereClase += propertyName + " = @" + propertyName + connector;
                    }
                    whereClase = whereClase.Substring(0, whereClase.Length - connector.Length);
                    sql += whereClase;

                    cmd.CommandText = sql;
                    cmd.Connection.Open();
                    DbDataReader reader = cmd.ExecuteReader();
                    data = FillReaderToObject<T>(reader);
                    cmd.Connection.Close();
                }
                catch (Exception e)
                {
                    ThrowDataAccessException(e);
                }
                finally
                {

                }
            }
            return data;
        }

        /// <summary>
        /// Filter equal
        /// </summary>
        /// <param name="propertyFilter"></param>
        /// <returns></returns>
        public IList<T> FilterEqual(T obj, Expression<Func<T, object>> propertyFilter)
        {
            IList<T> data = null;
            using (DbCommand cmd = Connection.GetCommand())
            {
                try
                {
                    string propertyName = GetPropertyNameByExpression(propertyFilter);
                    Type type = typeof(T);
                    PropertyInfo property = type.GetProperty(propertyName);
                    string sql = string.Format("SELECT * FROM [{0}] WHERE [{1}] = @v", type.Name, property.Name);
                    DbParameter param = cmd.CreateParameter();
                    param.ParameterName = "@v";
                    SetParameterValue(param, property.GetValue(obj, null));
                    cmd.Parameters.Add(param);

                    cmd.CommandText = sql;
                    cmd.Connection.Open();
                    DbDataReader reader = cmd.ExecuteReader();
                    data = FillReaderToList<T>(reader);
                    cmd.Connection.Close();
                }
                catch (Exception e)
                {
                    ThrowDataAccessException(e);
                }
                finally
                {

                }
            }
            return data;
        }

        /// <summary>
        /// Filter equal by data
        /// </summary>
        /// <param name="value"></param>
        /// <param name="propertyFilter"></param>
        /// <returns></returns>
        public IList<T> FilterEqualData(object value, Expression<Func<T, object>> propertyFilter)
        {
            IList<T> data = null;
            using (DbCommand cmd = Connection.GetCommand())
            {
                try
                {
                    string propertyName = GetPropertyNameByExpression(propertyFilter);
                    Type type = typeof(T);
                    PropertyInfo property = type.GetProperty(propertyName);
                    string sql = string.Format("SELECT * FROM [{0}] WHERE [{1}] = @v", type.Name, property.Name);
                    DbParameter param = cmd.CreateParameter();
                    param.ParameterName = "@v";
                    SetParameterValue(param, value);
                    cmd.Parameters.Add(param);

                    cmd.CommandText = sql;
                    cmd.Connection.Open();
                    DbDataReader reader = cmd.ExecuteReader();
                    data = FillReaderToList<T>(reader);
                    cmd.Connection.Close();
                }
                catch (Exception e)
                {
                    ThrowDataAccessException(e);
                }
                finally
                {

                }
            }
            return data;
        }

        /// <summary>
        /// Filter is null
        /// </summary>
        /// <param name="propertyFilter"></param>
        /// <returns></returns>
        public IList<T> FilterIsNulll(T obj, Expression<Func<T, object>> propertyFilter)
        {
            IList<T> data = null;
            using (DbCommand cmd = Connection.GetCommand())
            {
                try
                {
                    string propertyName = GetPropertyNameByExpression(propertyFilter);
                    Type type = typeof(T);
                    PropertyInfo property = type.GetProperty(propertyName);
                    string sql = string.Format("SELECT * FROM [{0}] WHERE [{1}] IS NULL", type.Name, property.Name);

                    cmd.CommandText = sql;
                    cmd.Connection.Open();
                    DbDataReader reader = cmd.ExecuteReader();
                    data = FillReaderToList<T>(reader);
                    cmd.Connection.Close();
                }
                catch (Exception e)
                {
                    ThrowDataAccessException(e);
                }
                finally
                {

                }
            }
            return data;
        }

        /// <summary>
        /// Get all data
        /// </summary>
        /// <typeparam name="K"></typeparam>
        /// <returns></returns>
        public IList<T> GetAllData()
        {
            IList<T> data = null;
            using (DbCommand cmd = Connection.GetCommand())
            {
                try
                {
                    Type type = typeof(T);
                    string sql = string.Format("SELECT * FROM [{0}]", type.Name);
                    cmd.CommandText = sql;
                    cmd.Connection.Open();
                    DbDataReader reader = cmd.ExecuteReader();
                    data = FillReaderToList<T>(reader);
                    cmd.Connection.Close();
                }
                catch (Exception e)
                {
                    ThrowDataAccessException(e);
                }
                finally
                {

                }
            }
            return data;
        }
        #endregion

        #region Insert Data
        public bool InsertData(T data)
        {
            int result = -1;
            using (DbCommand cmd = Connection.GetCommand())
            {
                try
                {
                    cmd.Connection.Open();
                    Type type = typeof(T);
                    string sqlColumns = "";
                    string sqlValues = "";
                    PropertyInfo[] properties = type.GetProperties();
                    int indexValue = 1;
                    PropertyInfo propertyIdentity = null;
                    foreach (PropertyInfo property in properties)
                    {
                        //dont insert auto incredent column data type
                        if (property.GetCustomAttributes(typeof(AutoIncrementAttribute), false).Length == 0)
                        {
                            DbParameter param = cmd.CreateParameter();
                            SetParameterValue(param, property.GetValue(data, null));
                            param.ParameterName = "@v" + indexValue;
                            sqlColumns += "[" + property.Name + "],";
                            sqlValues += param.ParameterName + ",";
                            indexValue++;
                            cmd.Parameters.Add(param);
                        }
                        else
                        {
                            propertyIdentity = property;
                        }
                    }
                    string sql = string.Format("INSERT INTO [{0}] ({1}) VALUES ({2})", type.Name, sqlColumns.TrimEnd(','), sqlValues.TrimEnd(','));
                    DbParameter paramIndentity = null;
                    if (propertyIdentity != null)
                    {
                        sql += " SELECT @identityValue = SCOPE_IDENTITY()";
                        paramIndentity = cmd.CreateParameter();
                        paramIndentity.DbType = System.Data.DbType.Int32;
                        paramIndentity.ParameterName = "@identityValue";
                        paramIndentity.Direction = System.Data.ParameterDirection.Output;
                        cmd.Parameters.Add(paramIndentity);
                    }
                    cmd.CommandText = sql;
                    result = cmd.ExecuteNonQuery();
                    if (result > 0 && propertyIdentity != null && paramIndentity != null)
                    {
                        int identity = (int)paramIndentity.Value;
                        propertyIdentity.SetValue(data, identity, null);
                    }
                    cmd.Connection.Close();
                }
                catch (Exception e)
                {
                    ThrowDataAccessException(e);
                }
                finally
                {

                }
            }
            return result > 0;
        }
        #endregion

        #region Update Data
        /// <summary>
        /// Update data
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public bool UpdateData(T data)
        {
            int result = -1;
            using (DbCommand cmd = Connection.GetCommand())
            {
                try
                {
                    cmd.Connection.Open();
                    Type type = typeof(T);
                    string sqlWHERE = "";
                    string sqlSET = "";
                    PropertyInfo[] properties = type.GetProperties();
                    int indexSET = 1;
                    int indexWHERE = 1;
                    foreach (PropertyInfo property in properties)
                    {
                        DbParameter param = cmd.CreateParameter();
                        SetParameterValue(param, property.GetValue(data, null));
                        //primary key
                        if (property.GetCustomAttributes(typeof(PrimaryKeyAttribute), false).Length > 0)
                        {
                            param.ParameterName = "@w" + indexSET;
                            sqlWHERE += string.Format("[{0}] = {1} AND ", property.Name, param.ParameterName);
                            indexWHERE++;
                        }
                        else
                        {
                            param.ParameterName = "@s" + indexSET;
                            sqlSET += string.Format("[{0}] = {1},", property.Name, param.ParameterName);
                            indexSET++;
                        }
                        cmd.Parameters.Add(param);
                    }

                    if (string.IsNullOrEmpty(sqlWHERE))
                    {
                        ThrowMissingWhereClause();
                    }
                    else
                    {
                        sqlWHERE = sqlWHERE.Substring(0, sqlWHERE.Length - 5);
                    }

                    string sql = string.Format("UPDATE [{0}] SET {1} WHERE {2}", type.Name, sqlSET.TrimEnd(','), sqlWHERE);
                    cmd.CommandText = sql;
                    result = cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception e)
                {
                    ThrowDataAccessException(e);
                }
                finally
                {

                }
            }
            return result > 0;
        }

        /// <summary>
        /// Update data with specificic fields
        /// </summary>
        /// <param name="data"></param>
        /// <param name="propertyValue"></param>
        /// <returns></returns>
        public bool UpdateDataColumns(T data, params Expression<Func<T, object>>[] propertyValue)
        {
            int result = -1;
            using (DbCommand cmd = Connection.GetCommand())
            {
                try
                {
                    cmd.Connection.Open();
                    string sqlWHERE = "";
                    string sqlSET = "";
                    int indexSET = 1;
                    int indexWHERE = 1;
                    Type type = typeof(T);
                    PropertyInfo[] properties = type.GetProperties();

                    foreach (Expression<Func<T, object>> propertyMember in propertyValue)
                    {
                        string propertyName = GetPropertyNameByExpression(propertyMember);
                        PropertyInfo property = type.GetProperty(propertyName);
                        //get set values
                        if (property.GetCustomAttributes(typeof(PrimaryKeyAttribute), false).Length == 0)
                        {
                            DbParameter param = cmd.CreateParameter();
                            SetParameterValue(param, property.GetValue(data, null));
                            param.ParameterName = "@s" + indexSET;
                            sqlSET += string.Format("[{0}] = {1},", property.Name, param.ParameterName);
                            cmd.Parameters.Add(param);
                            indexSET++;
                        }
                    }

                    //get where clause
                    foreach (PropertyInfo property in properties)
                    {
                        //primary key
                        if (property.GetCustomAttributes(typeof(PrimaryKeyAttribute), false).Length > 0)
                        {
                            DbParameter param = cmd.CreateParameter();
                            SetParameterValue(param, property.GetValue(data, null));
                            param.ParameterName = "@w" + indexWHERE;
                            sqlWHERE += string.Format("[{0}] = {1} AND ", property.Name, param.ParameterName);
                            cmd.Parameters.Add(param);
                            indexWHERE++;
                        }

                    }

                    if (string.IsNullOrEmpty(sqlWHERE))
                    {
                        throw new DBException("Where clause is missing") { Number = 1000, ExceptionType = DBExceptionType.WhereClauseMissing };
                    }
                    else
                    {
                        sqlWHERE = sqlWHERE.Substring(0, sqlWHERE.Length - 5);
                    }

                    string sql = string.Format("UPDATE [{0}] SET {1} WHERE {2}", type.Name, sqlSET.TrimEnd(','), sqlWHERE);
                    cmd.CommandText = sql;
                    result = cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception e)
                {
                    ThrowDataAccessException(e);
                }
                finally
                {

                }
            }

            return result > 0;
        }
        #endregion

        #region Delete data
        /// <summary>
        /// Delete data
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public bool DeleteData(T data)
        {
            int result = -1;
            using (DbCommand cmd = Connection.GetCommand())
            {
                try
                {
                    cmd.Connection.Open();
                    Type type = typeof(T);
                    string sqlWHERE = "";
                    PropertyInfo[] properties = type.GetProperties();
                    int indexWHERE = 1;
                    foreach (PropertyInfo property in properties)
                    {

                        //primary key
                        if (property.GetCustomAttributes(typeof(PrimaryKeyAttribute), false).Length > 0)
                        {
                            DbParameter param = cmd.CreateParameter();
                            SetParameterValue(param, property.GetValue(data, null));
                            param.ParameterName = "@w" + property.Name;
                            sqlWHERE += string.Format("[{0}] = {1},", property.Name, param.ParameterName);
                            cmd.Parameters.Add(param);
                            indexWHERE++;
                        }
                    }
                    string sql = string.Format("DELETE FROM [{0}] WHERE {1}", type.Name, sqlWHERE.TrimEnd(','));
                    cmd.CommandText = sql;
                    result = cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception e)
                {
                    ThrowDataAccessException(e);
                }
                finally
                {

                }
            }
            return result > 0;
        }
        #endregion

        #region Procedures
        #region Procedures - Get Data
        /// <summary>
        /// Get data from procedure
        /// </summary>
        /// <typeparam name="K">Type to get data</typeparam>
        /// <param name="procedureName">procedure name</param>
        /// <param name="param">parameter</param>
        /// <returns></returns>
        protected IList<T> ExecuteProcedureQuery(string procedureName, params DBParam[] param)
        {
            IList<T> data = null;
            using (DbCommand cmd = Connection.GetCommand())
            {
                try
                {
                    //check has params
                    if (param != null && param.Length > 0)
                    {
                        cmd.Parameters.AddRange(ConvertParamToParameters(cmd, param));
                    }
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = procedureName;
                    cmd.Connection.Open();
                    DbDataReader reader = cmd.ExecuteReader();
                    data = FillReaderToList<T>(reader);
                    cmd.Connection.Close();
                }
                catch (Exception e)
                {
                    ThrowDataAccessException(e);
                }
                finally
                {

                }
            }
            return data;
        }

        protected IList<K> ExecuteProcedureQuery<K>(string procedureName, params DBParam[] param)
        {
            IList<K> data = null;
            using (DbCommand cmd = Connection.GetCommand())
            {
                try
                {
                    //check has params
                    if (param != null && param.Length > 0)
                    {
                        cmd.Parameters.AddRange(ConvertParamToParameters(cmd, param));
                    }
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = procedureName;
                    cmd.Connection.Open();
                    DbDataReader reader = cmd.ExecuteReader();
                    data = FillReaderToList<K>(reader);
                    cmd.Connection.Close();
                }
                catch (Exception e)
                {
                    ThrowDataAccessException(e);
                }
                finally
                {

                }
            }
            return data;
        }
        #endregion

        #region Procedure non query
        /// <summary>
        /// Execute procedure non query
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        protected int ExecuteProcedureNoneQuery(string procedureName, params DBParam[] param)
        {
            int result = -1;
            using (DbCommand cmd = Connection.GetCommand())
            {
                try
                {
                    //check has params
                    if (param != null && param.Length > 0)
                    {
                        cmd.Parameters.AddRange(ConvertParamToParameters(cmd, param));
                    }
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = procedureName;
                    cmd.Connection.Open();
                    result = cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception e)
                {
                    ThrowDataAccessException(e);
                }
                finally
                {

                }
            }
            return result;
        }

        /// <summary>
        /// Execute procedure scalar
        /// </summary>
        /// <typeparam name="K"></typeparam>
        /// <param name="procedureName"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        protected K ExcecuteProcedureScalar<K>(string procedureName, params DBParam[] param)
        {
            K result = default(K);
            using (DbCommand cmd = Connection.GetCommand())
            {
                try
                {
                    //check has params
                    if (param != null && param.Length > 0)
                    {
                        cmd.Parameters.AddRange(ConvertParamToParameters(cmd, param));
                    }
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = procedureName;
                    cmd.Connection.Open();
                    result = (K)cmd.ExecuteScalar();
                    cmd.Connection.Close();
                }
                catch (Exception e)
                {
                    ThrowDataAccessException(e);
                }
                finally
                {

                }
            }
            return result;
        }
        #endregion

        #endregion

        #region Validate
        /// <summary>
        /// Validate object
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public IList<DetailValidationFail> ValidateObject(T obj)
        {
            IList<DetailValidationFail> listErrors = new List<DetailValidationFail>();
            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();
            foreach (PropertyInfo property in properties)
            {
                string propertyName = property.Name;
                object pValue = property.GetValue(obj, null);
                ValidationBaseAttribute[] attrs = (ValidationBaseAttribute[])property.GetCustomAttributes(typeof(MandatoryValidationAttribute), false);
                bool isManatory = true;
                if (attrs != null && attrs.Length > 0)
                {
                    if (!attrs[0].Validate(pValue))
                    {
                        listErrors.Add(new DetailValidationFail { PropertyName = propertyName, Message = attrs[0].GetErrorMessage(propertyName) });
                        isManatory = false;
                    }
                }

                ///check was input data before exec other validation
                if (isManatory)
                {
                    attrs = (ValidationBaseAttribute[])property.GetCustomAttributes(typeof(TextRangeValidationAttribute), false);
                    //check text length
                    if (attrs != null && attrs.Length > 0)
                    {
                        if (!attrs[0].Validate(pValue))
                        {
                            listErrors.Add(new DetailValidationFail { PropertyName = propertyName, Message = attrs[0].GetErrorMessage(propertyName) });
                        }
                    }

                    attrs = (ValidationBaseAttribute[])property.GetCustomAttributes(typeof(NumberRangeValidationAttribute), false);
                    //check text length
                    if (attrs != null && attrs.Length > 0)
                    {
                        if (!attrs[0].Validate(pValue))
                        {
                            listErrors.Add(new DetailValidationFail { PropertyName = propertyName, Message = attrs[0].GetErrorMessage(propertyName) });
                        }
                    }

                    attrs = (ValidationBaseAttribute[])property.GetCustomAttributes(typeof(EmailValidationAttribute), false);
                    //check text length
                    if (attrs != null && attrs.Length > 0)
                    {
                        if (!attrs[0].Validate(pValue))
                        {
                            listErrors.Add(new DetailValidationFail { PropertyName = propertyName, Message = attrs[0].GetErrorMessage(propertyName) });
                        }
                    }
                }
            }

            return listErrors;
        }
        #endregion

        #region Helper methods
        /// <summary>
        /// Fill reader to object
        /// </summary>
        /// <typeparam name="K"></typeparam>
        /// <param name="reader"></param>
        /// <returns></returns>
        protected static K FillReaderToObject<K>(DbDataReader reader)
        {
            K instance = default(K);
            Type type = typeof(K);
            PropertyInfo[] properties = type.GetProperties();
            while (reader.Read())
            {
                instance = Activator.CreateInstance<K>();
                foreach (PropertyInfo property in properties)
                {
                    object data = reader[property.Name];
                    if (data == DBNull.Value)
                    {
                        data = null;
                    }
                    property.SetValue(instance, data, null);
                }
                break;
            }
            //close and realease resource
            reader.Close();
            return instance;
        }

        /// <summary>
        /// Fill reader to list
        /// </summary>
        /// <typeparam name="K"></typeparam>
        /// <param name="reader"></param>
        /// <returns></returns>
        protected IList<K> FillReaderToList<K>(DbDataReader reader)
        {
            IList<K> list = new List<K>();
            Type type = typeof(K);
            PropertyInfo[] properties = type.GetProperties();
            while (reader.Read())
            {
                K instance = Activator.CreateInstance<K>();
                foreach (PropertyInfo property in properties)
                {
                    object data = reader[property.Name];
                    if (data == DBNull.Value)
                    {
                        data = null;
                    }
                    property.SetValue(instance, data, null);
                }
                list.Add(instance);
            }
            //close and realease resource
            reader.Close();
            return list;
        }

        /// <summary>
        /// Convert normal param to parameters
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        protected DbParameter[] ConvertParamToParameters(DbCommand cmd, DBParam[] param)
        {
            DbParameter[] parameters = new DbParameter[param.Length];
            int index = 0;
            foreach (DBParam p in param)
            {
                parameters[index] = cmd.CreateParameter();
                parameters[index].ParameterName = p.Name;
                parameters[index].Value = p.Value;
                index++;
            }
            return parameters;
        }

        /// <summary>
        /// Set parameter
        /// </summary>
        /// <param name="param"></param>
        /// <param name="value"></param>
        protected void SetParameterValue(DbParameter param, object value)
        {
            if (value == null)
            {
                param.Value = DBNull.Value;
            }
            else
            {
                param.Value = value;
            }
        }

        /// <summary>
        /// Get property name by expression
        /// </summary>
        /// <param name="expression"></param>
        /// <returns></returns>
        protected string GetPropertyNameByExpression(Expression<Func<T, object>> expression)
        {
            string propertyName = null;
            if (expression.Body.NodeType == ExpressionType.Convert)
            {
                var valueMember = ((UnaryExpression)expression.Body).Operand;
                propertyName = ((MemberExpression)valueMember).Member.Name;
            }
            else
            {
                var valueMember = expression.Body as MemberExpression;
                propertyName = valueMember.Member.Name;
            }
            return propertyName;
        }

        /// <summary>
        /// Throw missing where clause
        /// </summary>
        /// <returns></returns>
        private string ThrowMissingWhereClause()
        {
            throw new DBException("Where clause is missing") { Number = 1000, ExceptionType = DBExceptionType.WhereClauseMissing };
        }

        /// <summary>
        /// Throw exception
        /// </summary>
        /// <param name="e"></param>
        private void ThrowDataAccessException(Exception e)
        {
            if (e is SqlException)
            {
                SqlException sqlExeption = (SqlException)e;
                if (sqlExeption.Number == 2627)
                {
                    throw new DBException(e.Message) { Number = sqlExeption.Number, ExceptionType = DBExceptionType.DuplicateData };
                }
                else
                {
                    throw new DBException(e.Message) { Number = sqlExeption.Number, ExceptionType = DBExceptionType.ConnectionProblem };
                }
            }
            else if (e is DBException)
            {
                throw e;
            }
            else
            {
                throw new DBException(e.Message) { Number = 1001, ExceptionType = DBExceptionType.UnknowProblem };
            }
        }
        #endregion
    }
}
